package com.swn.common.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Types;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Repository;

@Repository
public class ExcelTools {
	private static Logger logger = Logger.getLogger("ExcelTools.class");
	// 项目路径
	private static final String proPath =  StaticParam.getWebRootPath(); 
	// 模板路径
	private static final String basePath = proPath + "file\\excel\\template\\";
	// 导出路径
	private static final String outPath = proPath + "file\\excel\\download\\";
	// 单元格高度
	private static final short cellHeight = 20;

	/**
	 * 根据excel模板生成excel
	 * @param sql
	 * @param templateName：模版名称，包括扩展名
	 * @param createExcelName：生成文件名称，，包括扩展名
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */  
	  
	
	public static String outputExcel(String sql, String templateName,String createExcelName) throws FileNotFoundException, IOException {
		// 获取模板
		XSSFWorkbook wb = getTemplateFile(templateName);
		JdbcTemplate jdbcTemplate	= (JdbcTemplate)ServiceLocator.getBean("jdbcTemplate");
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);
		insertTable(wb, sqlRowSet);
		boolean bl = createFile(wb, createExcelName);
		if (bl) {
			return outPath + createExcelName;
		} else {
			return "";
		}

	}

	/**
	 * 
	 * 根据excel模板生成excel
	 * @param sql
	 * @param templateName：模版名称，包括扩展名
	 * @param createExcelName：生成文件名称，，包括扩展名
	 * @param paramMap：需要替换的变量。如将excel模板{year}替换，则设置一个key为year的map
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String outputExcel(String sql, String templateName,
			String createExcelName, Map<String, String> paramMap)
			throws FileNotFoundException, IOException {
		// 获取模板
		XSSFWorkbook wb = getDynamicTemplateExcel(templateName, paramMap);
		JdbcTemplate jdbcTemplate	= (JdbcTemplate)ServiceLocator.getBean("jdbcTemplate");
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);
		insertTable(wb, sqlRowSet);
		boolean bl = createFile(wb, createExcelName);
		if (bl) {
			return outPath + createExcelName;
		} else {
			return "";
		}
	}
	
	//计算从firstRowNum到firstRowNum+10行之间最大的有效列（考虑复杂表头最多占10列，一般到不了）
	private static int getPhysicalNumberOfCells(XSSFSheet sheet,int firstRowNum){
		int maxPhysicalNumberOfCells = 0 ;//最大有效列
		for (int i = firstRowNum; i <= firstRowNum+10; i++) {
			XSSFRow hssfRow = sheet.getRow(i);
			if(hssfRow!=null){
				// 实际有效的列（不包括空列）
				int columnNum = hssfRow.getPhysicalNumberOfCells();
				if(columnNum>maxPhysicalNumberOfCells){
					maxPhysicalNumberOfCells = columnNum;
				}
			}
		}
		
		return maxPhysicalNumberOfCells;
	}
	

	/**
	 * 动态表头替换
	 * 
	 * @param templateName
	 * @param paramMap
	 *            动态数据 ，如将excel模板{year}替换，则设置一个key为year的map
	 * @return
	 */
	private static XSSFWorkbook getDynamicTemplateExcel(String templateName,
			Map<String, String> paramMap) {
		XSSFWorkbook wb = getTemplateFile(templateName);

		// 第一个不为空的行
		int firstRowNum = wb.getSheetAt(0).getFirstRowNum();
		// 最后一个不为空的行
		int lastRowNum = wb.getSheetAt(0).getLastRowNum();

		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow hssfRow = sheet.getRow(firstRowNum);
		// 实际有效的列（不包括空列）
		//int columnNum = hssfRow.getPhysicalNumberOfCells();
		
		//获取最多的有效列
		int columnNum = getPhysicalNumberOfCells(sheet,firstRowNum);
		
		// 第一个不为空的列
		int firstCellNum = hssfRow.getFirstCellNum();
		// 最后一个不为空的列
		int lastCellNum = firstCellNum + columnNum - 1;

		Row row = null;
		Cell cell = null;
		String value = "";
		String[] splits;
		String[] splits2;
		for (int i = firstRowNum; i <= lastRowNum; i++) {
			row = sheet.getRow(i);
			// 不为空行，则下下执行。有的中间有空行，则为null
			if (row != null) {
				for (int j = firstCellNum; j <= lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell != null) {
						value = cell.getStringCellValue();
						if (value.contains("{")) {
							splits = value.split("\\{");
							for (String str : splits) {
								if (str.contains("}")) {
									splits2 = str.split("\\}");
									cell.setCellValue(value.replaceAll("\\{"
											+ splits2[0] + "\\}",
											paramMap.get(splits2[0])));
								}
							}
						}
					}
				}
			}
		}
		return wb;
	}

	/**
	 * 获取POI的Excel文件封装对象,适用于Excel2007+
	 * 
	 * @param 模板名称
	 *            (确保模板在模板路径下)
	 * @return POI的Excel文件封装对象
	 */
	private static XSSFWorkbook getTemplateFile(String name) {
		String finalPath = basePath + name;
		XSSFWorkbook wb;
		try {
			wb = new XSSFWorkbook(new FileInputStream(finalPath));
			return wb;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			logger.error("文件路径错误,请检查模板路径." + finalPath);
			return new XSSFWorkbook();
		} catch (IOException e) {
			logger.error("IO异常,请检查模板文件." + finalPath);
			return new XSSFWorkbook();
		}
	}

	// 生成文件到download
	private static boolean createFile(XSSFWorkbook wb, String createExcelName) {
		boolean bl = false;
		try {
			wb.write(new FileOutputStream(outPath + createExcelName));
			bl = true;
		} catch (Exception e) {
			logger.error( e);
		}
		return bl;
	}

	/**
	 * 把数据插入sql
	 * 
	 * @param wb
	 * @param sqlRowSet
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	private static XSSFWorkbook insertTable(XSSFWorkbook wb, SqlRowSet sqlRowSet)
			throws FileNotFoundException, IOException {
		// 第一个不为空的行
		int firstRowNum = wb.getSheetAt(0).getFirstRowNum();
		// 最后一个不为空的行
		int lastRowNum = wb.getSheetAt(0).getLastRowNum();

		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow hssfRow = sheet.getRow(firstRowNum);
		// 实际有效的列（不包括空列）
		//int columnNum = hssfRow.getPhysicalNumberOfCells();
		// 第一个不为空的列
		int firstCellNum = hssfRow.getFirstCellNum();
		// int lastCellNum = hssfRow.getLastCellNum();有问题，自己计算
		// 最后一个不为空的列
		//int lastCellNum = firstCellNum + columnNum - 1;

		Row row = null;
		Cell cell = null;
		int rowNum = 0;
		while (sqlRowSet.next()) {// 循环记录
			row = sheet.createRow(rowNum + lastRowNum + 1);
			rowNum++;
			row.setHeightInPoints(cellHeight);
			SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
			int iCols = sqlRowSetMetaData.getColumnCount();

			for (int i = 1, j = firstCellNum; i <= iCols; i++, j++) {// 循环列并未Excel单元格赋值
				// 数字格式化
				XSSFCellStyle st = wb.createCellStyle();

				st.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
				setBorder(st);

				cell = row.createCell(j);
				String value = sqlRowSet.getString(i);
				if (sqlRowSetMetaData.getColumnType(i) == Types.CHAR
						|| sqlRowSetMetaData.getColumnType(i) == Types.VARCHAR) {
					cell.setCellValue(value);
					st.setAlignment(XSSFCellStyle.ALIGN_CENTER);
					// 这样才能真正的控制单元格格式，@就是指文本型，具体格式的定义还是参考上面的原文吧
					st.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));

				} else if (sqlRowSetMetaData.getColumnType(i) == Types.INTEGER) {
					st.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
					// 数值类型
					cell.setCellValue(Integer.parseInt(value));
				} else if (sqlRowSetMetaData.getColumnType(i) == Types.DECIMAL
						|| sqlRowSetMetaData.getColumnType(i) == Types.NUMERIC
						|| sqlRowSetMetaData.getColumnType(i) == Types.INTEGER
						|| sqlRowSetMetaData.getColumnType(i) == Types.FLOAT
						|| sqlRowSetMetaData.getColumnType(i) == Types.DOUBLE) {  
					
				} else {
					st.setAlignment(XSSFCellStyle.ALIGN_CENTER);
					cell.setCellValue(value);
				}
				cell.setCellStyle(st);

			}

		}
		return wb;
	}

	/**
	 * 设置边框
	 * 
	 * @param style
	 * @return
	 */
	public static CellStyle setBorder(CellStyle style) {
		style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(CellStyle.BORDER_THIN);// 右边框
		return style;
	}
	/**
	 * 设置字体
	 * 
	 * @param style
	 * @return
	 */
	/*
	 * public CellStyle setFont(CellStyle style){ HSSFFont font =
	 * workbook.createFont(); font.setFontHeightInPoints((short) 12);
	 * font.setFontName("仿宋"); font.setBold(true); style.setFont(font); return
	 * style; }
	 */
}
